//**********************************************************
//    File Name: Utility.cs
//    Author: Team Motif
//    Date Created : 04/13/2007
//    Current Owner: Team Motif 
//                                                                            
//    Purpose: 
//    Database Queries, Database Connection, Queries formation
//    This file will contain functions which actually connects 
//    to the database. None of the classes will talk directly 
//    to the database except for this file
//	  Copyright 2007
//**********************************************************
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data.OleDb;
using System.Globalization;
using System.Data;


namespace Library
{
    class Utility
    {
        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Default constructor for the Utility Class. It calls the DBConnection function
        /// -------------------------------------------------------------------------------
        /// </summary>
        public 
        Utility()
        {
            DBConnection();
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Static global method which will return a connection string to the SQL database
        /// -------------------------------------------------------------------------------
        /// </summary>
        public static string GetSqlConnectionString()
        {
            System.Object lockThis = new System.Object();
            lock (lockThis)
            {
                //Connection String
                return ("Initial Catalog = EHNW;Data Source = localhost;Integrated Security = True;");
            }
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function which connects this application to the database.
        /// -------------------------------------------------------------------------------
        /// </summary>
        public void DBConnection()
        {
            string TempConnectionString = GetSqlConnectionString();
            connection = new SqlConnection(TempConnectionString);
        }


        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that saves a new Appointment to the database
        /// -------------------------------------------------------------------------------
        /// </summary>
        public bool SaveCreatedAppointment(long clientID, long hcwID, DateTime start, DateTime end)
        {
            String Query;

            try
            {
                connection.Open();
                // Insert the record in Appointment table.
                Query = "INSERT INTO Appointment (ClientID,HCWID,StartTime,EndTime, " +
                          " ActualStartTime,ActualEndTime,MlgPrevClient,MlgThisClient,ApptStatus) " +
                          "values(" + clientID + ", " + hcwID + ", '" + start + "', '" + end + "', " +
                          "NULL," + "NULL," + "NULL," + "NULL,'" + "A');";

                System.Data.SqlClient.SqlCommand InsCommand1 = new System.Data.SqlClient.SqlCommand(Query, connection);
                InsCommand1.ExecuteNonQuery();

            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::SaveCreatedAppointment Exception handled : " + ExceptionMsg.ToString());
                return false;
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return true;
        }
        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that saves a new client to the database
        /// -------------------------------------------------------------------------------
        /// </summary>
        public bool SaveClientToDataBase(Client tmpClientParam)
        {
            String Query;
            try
            {
                // Insert the record in UserDetails table.
                Query = "INSERT INTO UserDetails (LastName,FirstName,MiddleName,DOB,Sex,UserType, " +
                          " UserStatus,Address1,Address2,City,State,ZipCode,PhoneNumber1,PhoneNumber2, " +
                          " PhoneNumber3,Email)" +
                          "values('" + tmpClientParam.GetLastName() + "', " +
                          "'" + tmpClientParam.GetFirstName() + "', " +
                          "'" + tmpClientParam.GetMiddleName() + "', " +
                              tmpClientParam.GetDateOfBirth().ToShortDateString() + "," +
                          "'" + ((tmpClientParam.GetSexType().Equals(SexType.Male)) ? "M" : "F") + "', " +
                          "'" + "C" + "', " +
                          "'" + "A" + "', " +
                          "'" + tmpClientParam.GetAddress1() + "', " +
                          "'" + tmpClientParam.GetAddress2() + "', " +
                          "'" + tmpClientParam.GetCity() + "', " +
                          "'" + tmpClientParam.GetState() + "', " +
                          "'" + tmpClientParam.GetZipCode().ToString() + "', " +
                          "'" + tmpClientParam.GetPhoneNumber1().ToString() + "', " +
                          "'" + tmpClientParam.GetPhoneNumber2().ToString() + "', " +
                          "'" + tmpClientParam.GetPhoneNumber3().ToString() + "', " +
                          "'" + tmpClientParam.GetEmail() + "');";

                        SqlDataAdapter da = new SqlDataAdapter(Query, connection);
                        DataSet ds = new DataSet();
                        da.Fill(ds, "UserDetails");

                        //Insert the record in Worker table

                        Query = "SELECT UserID FROM UserDetails WHERE LastName= '" + tmpClientParam.GetLastName() + "'" +
                                         "AND FirstName = '" + tmpClientParam.GetFirstName() + "'" +
                                         "AND DOB = " + tmpClientParam.GetDateOfBirth().ToShortDateString() + ";";
                        da = new SqlDataAdapter(Query, connection);
                        ds = new DataSet("UserDetails ");
                        da.Fill(ds, "UserDetails ");

                        long tmpClientID;
                        tmpClientID = int.Parse(ds.Tables[0].Rows[0].ItemArray[0].ToString());

                        Query = "INSERT INTO Client (ClientID,ClientType,WeeklyHrs, HourlyRate,Copay,SpecialNeeds,IntialClientData) " +
                                " VALUES(" + tmpClientID + ", 'P', null, null, null, null, null);" ;

                        da = new SqlDataAdapter(Query, connection);
                        ds = new DataSet();
                        da.Fill(ds, "Client");
                        return true;

            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::SaveClient Exception handled : " + ExceptionMsg.ToString());
                return false;
            }

        }
        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that saves a new homecare worker to the database
        /// -------------------------------------------------------------------------------
        /// </summary>
        public bool SaveWorker(HomeCareWorker tmpWorkerParam)
        {
            String Query;

            if (tmpWorkerParam.GetUserID() == -1)  // New Worker is being added
            {
                try
                {
                    // Insert the record in UserDetails table.
                    Query = "INSERT INTO UserDetails (LastName,FirstName,MiddleName,DOB,Sex,UserType, " +
                              " UserStatus,Address1,Address2,City,State,ZipCode,PhoneNumber1,PhoneNumber2, " +
                              " PhoneNumber3,Email)" +
                              "values('" + tmpWorkerParam.GetLastName() + "', " +
                              "'" + tmpWorkerParam.GetFirstName() + "', " +
                              "'" + tmpWorkerParam.GetMiddleName() + "', " +
                              "'" + tmpWorkerParam.GetDateOfBirth().ToShortDateString() + "'," +
                              "'" + ((tmpWorkerParam.GetSexType().Equals(SexType.Male)) ? "M" : "F") + "', " +
                              "'" + "W" + "', " +
                              "'" + "A" + "', " +
                              "'" + tmpWorkerParam.GetAddress1() + "', " +
                              "'" + tmpWorkerParam.GetAddress2() + "', " +
                              "'" + tmpWorkerParam.GetCity() + "', " +
                              "'" + tmpWorkerParam.GetState() + "', " +
                              "'" + tmpWorkerParam.GetZipCode().ToString() + "', " +
                              "'" + tmpWorkerParam.GetPhoneNumber1().ToString() + "', " +
                              "'" + tmpWorkerParam.GetPhoneNumber2().ToString() + "', " +
                              "'" + tmpWorkerParam.GetPhoneNumber3().ToString() + "', " +
                              "'" + tmpWorkerParam.GetEmail() + "');";

                    System.Data.SqlClient.SqlCommand InsCommand1 = new System.Data.SqlClient.SqlCommand(Query, connection);
                    InsCommand1.ExecuteNonQuery();

                    //Insert the record in Worker table

                    Query = "SELECT UserID FROM UserDetails WHERE LastName= '" + tmpWorkerParam.GetLastName() + "'" +
                                     "AND FirstName = '" + tmpWorkerParam.GetFirstName() + "'" +
                                     "AND DOB = '" + tmpWorkerParam.GetDateOfBirth().ToShortDateString() + "';";
                    SqlDataAdapter da = new SqlDataAdapter(Query, connection);
                    DataSet ds = new DataSet("UserDetails ");
                    da.Fill(ds, "UserDetails ");


                    long tmpWorkerID;
                    tmpWorkerID = int.Parse(ds.Tables[0].Rows[0].ItemArray[0].ToString());

                    //Insert the record(s) in WorkerConstriants table.
                    Query = "INSERT INTO Worker (HCWID,Constraints,WeeklyHrs) VALUES(" +
                              tmpWorkerID + "," +
                              "'" + tmpWorkerParam.constraints + "'," +
                              tmpWorkerParam.weeklyHrs + ");";

                    InsCommand1 = new System.Data.SqlClient.SqlCommand(Query, connection);
                    InsCommand1.ExecuteNonQuery();


                    //Insert the record(s) in WorkerSkills table.

                    foreach (Task tmpTask in tmpWorkerParam.GetWorkerTasks())
                    {
                        Query = "INSERT INTO WorkerSkills (HCWID,TaskID) VALUES(" +
                                  tmpWorkerID + "," +
                                  tmpTask.GetTaskID() + ");";

                        InsCommand1 = new System.Data.SqlClient.SqlCommand(Query, connection);
                        InsCommand1.ExecuteNonQuery();
                    }

                }
                catch (Exception ExceptionMsg)
                {
                    LOG.error("HomeCareWorker::SaveWorker Exception handled : " + ExceptionMsg.ToString());
                }

                // Insert the record in WorkerCertification table.

            }
            else  // Worker is already existing and the information must be just updated.
            {
                //UpdateHomeCareWorker();
            }

            return true;
        }


        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that returns all the records from AvailabilityDetails between a 
        /// particular date and for a particular client
        /// -------------------------------------------------------------------------------
        /// </summary>
        public ArrayList ReadAvailDetailsForClient(long tmpClientIDParam, DateTime currentDate, DateTime nextDate)
        {
            ArrayList tmpslotsArr = new ArrayList();

        
            try
            {
                connection.Open();
                
                String Query;
                //Creating the query according to the input
                Query = "SELECT AvailabilityDetails.Day,StartDate,EndDate,StartHour,StartMinute," +
                          " EndHour,EndMinute FROM " +
                          " AvailabilityDetails,AvailabilityMaster " +
                          " WHERE AvailabilityMaster.UserID = " + tmpClientIDParam +
                          " AND AvailabilityMaster.AvlID = AvailabilityDetails.AvlID AND" +
                          " ((StartDate <= '" + currentDate + "' AND EndDate >='" + currentDate + "') OR " +
                          " (StartDate >= '" + currentDate + "' AND StartDate <='" + nextDate + "'));";

                SqlDataAdapter da = new SqlDataAdapter(Query, connection);

                DataSet ds = new DataSet("AvailabilityDetails");
                da.Fill(ds, "AvailabilityDetails");

                //Creating an arraylist with the returned records
                for (int iCntr = 0; iCntr < ds.Tables[0].Rows.Count; iCntr++)
                {
                    AvailableTimeSlot tmpslots = new AvailableTimeSlot();

                    System.Globalization.CultureInfo info = new System.Globalization.CultureInfo("en-US", false);
                    System.Globalization.Calendar calendar = info.Calendar;

                    String dayOfWeek = ds.Tables[0].Rows[iCntr].ItemArray[0].ToString();

                    //dateForDay returns returns date for the day
                    DateTime dateForDay = AvailableTimeSlot.GetDateForDay(currentDate, dayOfWeek);

                    //Creating a start date object
                    int tmpYear = DateTime.Parse(ds.Tables[0].Rows[iCntr].ItemArray[1].ToString()).Year;
                    int tmpMonth = DateTime.Parse(ds.Tables[0].Rows[iCntr].ItemArray[1].ToString()).Month;
                    int tmpDate = DateTime.Parse(ds.Tables[0].Rows[iCntr].ItemArray[1].ToString()).Day;

                    DateTime startDate = new DateTime(tmpYear,tmpMonth,tmpDate);

                    //Check if date > start date and Day < nextdate
                    if (dateForDay >= startDate && dateForDay <= nextDate)
                    {
                        
                        int tmpHour = int.Parse(ds.Tables[0].Rows[iCntr].ItemArray[3].ToString());
                        int tmpMins = int.Parse(ds.Tables[0].Rows[iCntr].ItemArray[4].ToString());

                        System.DateTime StartDateTime = new System.DateTime(dateForDay.Year, dateForDay.Month, dateForDay.Day, tmpHour, tmpMins, 0, calendar);
                    
                        tmpHour = int.Parse(ds.Tables[0].Rows[iCntr].ItemArray[5].ToString());
                        tmpMins = int.Parse(ds.Tables[0].Rows[iCntr].ItemArray[6].ToString());

                        System.DateTime EndDateTime = new System.DateTime(dateForDay.Year, dateForDay.Month, dateForDay.Day, tmpHour, tmpMins, 0, calendar);

                        tmpslots.SetBeginDateTime(StartDateTime);
                        tmpslots.SetEndDateTime(EndDateTime);
                        tmpslotsArr.Add(tmpslots);
                    }

                }
            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::ReadAvailDetailsForClient Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return tmpslotsArr;
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that Read the client information depending on the firstname and/or the 
        /// last name
        /// -------------------------------------------------------------------------------
        /// </summary>
        public ArrayList ReadClientList(String tmpFirstNameParam, String tmpLastNameParam)
        {
            ArrayList clientList = new ArrayList();
            String Query;

            try
            {
                connection.Open();
                //Pass the connection to a command object
                //Creating the query according to the input
                if (String.IsNullOrEmpty(tmpFirstNameParam) && String.IsNullOrEmpty(tmpLastNameParam))
                {
                    Query = "SELECT UserID, UserDetails.FirstName, UserDetails.LastName, UserDetails.City, " +
                              " UserDetails.State, UserDetails.DOB " +
                              " FROM UserDetails WHERE UserType = 'C';"; 
                }
                else if (String.IsNullOrEmpty(tmpFirstNameParam))
                {
                    Query = "SELECT UserID, UserDetails.FirstName, UserDetails.LastName, UserDetails.City, " +
                              " UserDetails.State, UserDetails.DOB " +
                              " FROM UserDetails WHERE upper(UserDetails.LastName) = upper('" + tmpLastNameParam + "') " +
                              " AND UserType = 'C';";
                }
                else if (String.IsNullOrEmpty(tmpLastNameParam))
                {
                    Query = "SELECT UserID, UserDetails.FirstName, UserDetails.LastName, UserDetails.City, " +
                              " UserDetails.State, UserDetails.DOB " +
                              " FROM UserDetails WHERE upper(UserDetails.FirstName) = upper('" + tmpFirstNameParam + "') " +
                              " AND UserType = 'C';";
                }
                else
                {
                    Query = "SELECT UserID, UserDetails.FirstName, UserDetails.LastName, UserDetails.City, " +
                              " UserDetails.State, UserDetails.DOB" +
                              " FROM UserDetails WHERE upper(UserDetails.FirstName) = upper('" + tmpFirstNameParam +
                              "') " + " AND upper(UserDetails.LastName)=upper('" + tmpLastNameParam + "') " +
                              " AND UserType = 'C';";
                }

                SqlDataAdapter da = new SqlDataAdapter(Query, connection);

                DataSet ds = new DataSet();
                da.Fill(ds, "UserDetails");

                //Get the data from the input and store it in Client Object
                for (int iCntr = 0; iCntr < ds.Tables[0].Rows.Count; iCntr++)
                {
                    Client ClientObj = new Client();

                    long lngID = long.Parse(ds.Tables[0].Rows[iCntr].ItemArray[0].ToString());
                    ClientObj.SetUserID(lngID);

                    ClientObj.SetName(ds.Tables[0].Rows[iCntr].ItemArray[1].ToString(), ds.Tables[0].Rows[iCntr].ItemArray[2].ToString());
                    ClientObj.SetCity(ds.Tables[0].Rows[iCntr].ItemArray[3].ToString());
                    ClientObj.SetState(ds.Tables[0].Rows[iCntr].ItemArray[4].ToString());

                    DateTime myDateTime = DateTime.Parse(ds.Tables[0].Rows[iCntr].ItemArray[5].ToString());
                    ClientObj.SetDateOfBirth(myDateTime);

                    clientList.Add(ClientObj);
                }

            }
            catch (Exception ExceptionMsg)
            {
                //LOG.error("Utility::ReadClientList Exception handled: " + ExceptionMsg.ToString());
                ArrayList EmptyClientList = new ArrayList();
                clientList = EmptyClientList;
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return clientList;
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that Delete Clients from Database
        /// -------------------------------------------------------------------------------
        /// </summary>
        public bool DeleteClientsFromDB(long tmpClientID, String tmpFirstName, String tmpLastName)
        {
            String Query;
            bool ReturnVal = false;
            //Connection to the database. Needs to do this in seperate File */
            try
            {
                connection.Open();

                //Form the query
                //if ClientID != 0 that means user wants to delete a particular record
                if (tmpClientID != 0)
                {
                    SqlCommand tmpSQLCommand = new SqlCommand();
                    tmpSQLCommand.Connection = connection;

                    //if ClientID <> 0 that means user wants to delete only a particular records
                    tmpSQLCommand.CommandText = "DELETE FROM Client WHERE UserID = @tmpUserID";
                    tmpSQLCommand.Parameters.Add(new SqlParameter("@tmpUserID", tmpClientID));
                    tmpSQLCommand.ExecuteNonQuery();

                    tmpSQLCommand.CommandText = "DELETE FROM UserDetails WHERE UserType ='C' AND UserID = @tmpUserID";
                    tmpSQLCommand.Parameters.Add(new SqlParameter("@tmpUserID", tmpClientID));
                    tmpSQLCommand.ExecuteNonQuery();
                }
                else
                {
                    //When all the clientID, firstname and last name is null, then delete all records
                    if (tmpClientID == 0 && String.IsNullOrEmpty(tmpFirstName) && (String.IsNullOrEmpty(tmpLastName)))
                    {
                        Query = "DELETE FROM Client ;";
                        SqlCommand tmpSQLCommand = new SqlCommand(Query, connection);
                        tmpSQLCommand.ExecuteNonQuery();

                        Query = "Delete FROM UserDetails where UserType ='C'";
                        SqlCommand tmpSQLCommand1 = new SqlCommand(Query, connection);
                        tmpSQLCommand1.ExecuteNonQuery();
                    }
                    else if (!String.IsNullOrEmpty(tmpFirstName) && !(String.IsNullOrEmpty(tmpLastName)))
                    {
                        //This will get executed when ClientID=0 and Fristname and Lastname has values
                        Query = "DELETE FROM CLIENT WHERE CLIENTID = (SELECT USERID FROM UserDetails WHERE FirstName='" +
                                tmpFirstName + "' AND LastName = '" + tmpLastName + "');";
                        SqlCommand tmpSQLCommand = new SqlCommand(Query, connection);
                        tmpSQLCommand.ExecuteNonQuery();

                        Query = "DELETE FROM USERDETAILS WHERE UserType ='C' AND FirstName='" +
                                tmpFirstName + "' AND LastName = '" + tmpLastName + "';";
                        SqlCommand tmpSQLCommand1 = new SqlCommand(Query, connection);
                        tmpSQLCommand1.ExecuteNonQuery();

                    }
                    else if (String.IsNullOrEmpty(tmpFirstName))
                    {
                        //This will get executed when ClientID=0 and Fristname = null and Lastname has values
                        Query = "DELETE FROM CLIENT WHERE CLIENTID = (SELECT USERID FROM UserDetails WHERE LastName = '" +
                                 tmpLastName + "');";
                        SqlCommand tmpSQLCommand = new SqlCommand(Query, connection);
                        tmpSQLCommand.ExecuteNonQuery();

                        Query = "DELETE FROM USERDETAILS WHERE UserType ='C' AND LastName='" + tmpLastName + "';";
                        SqlCommand tmpSQLCommand1 = new SqlCommand(Query, connection);
                        tmpSQLCommand1.ExecuteNonQuery();
                    }
                    else if (String.IsNullOrEmpty(tmpLastName))
                    {
                        //This will get executed when ClientID=0 and Lastname = null and Firstname has values
                        Query = "DELETE FROM CLIENT WHERE CLIENTID = (SELECT USERID FROM UserDetails WHERE FirstName = '" +
                                 tmpFirstName + "');";
                        SqlCommand tmpSQLCommand = new SqlCommand(Query, connection);
                        tmpSQLCommand.ExecuteNonQuery();

                        Query = "DELETE FROM USERDETAILS WHERE UserType ='C' AND FirstName='" + tmpFirstName + "';";
                        SqlCommand tmpSQLCommand1 = new SqlCommand(Query, connection);
                        tmpSQLCommand1.ExecuteNonQuery();
                    }
                }
                ReturnVal = true;
            }
            catch (SqlException ExceptionMsg)
            {
                LOG.error("Utility::DeleteAllClientsFromDB Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return ReturnVal;
        }
        
        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that Get all the tasks for particular ClientID
        /// -------------------------------------------------------------------------------
        /// </summary>
        public ArrayList ReadAllTasksForClient(long tmpClientIDParam)
        {
            ArrayList ArrTaskList = new ArrayList();
            String Query;
            //Connection to the database. Needs to do this in seperate File */
            try
            {
                connection.Open();
                //Form the query
                Query = "SELECT ClientNeeds.TaskID, TaskName FROM Task, ClientNeeds " +
                        " WHERE ClientNeeds.ClientID =  " + tmpClientIDParam + " AND ClientNeeds.TaskID = Task.TaskID;";

                SqlDataAdapter da = new SqlDataAdapter(Query, connection);

                DataSet ds = new DataSet();
                da.Fill(ds, "Task");

                //Put the result back in the task object 
                for (int iCntr = 0; iCntr < ds.Tables[0].Rows.Count; iCntr++)
                {
                    Task TaskObj = new Task();

                    TaskObj.SetTaskID(int.Parse(ds.Tables[0].Rows[iCntr].ItemArray[0].ToString()));
                    TaskObj.SetTaskName(ds.Tables[0].Rows[iCntr].ItemArray[1].ToString());
                    ArrTaskList.Add(TaskObj);
                }
            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::ReadAllTasksForClient Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return ArrTaskList;
        }


        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that Get the Number of Approved Hours and Number of Scheduled hours for
        /// Client along with the special Needs of the Client
        /// -------------------------------------------------------------------------------
        /// </summary>
        public ArrayList ReadClientNeedsAndWeekelyHours(long tmpClientIDParam)
        {
            ArrayList tmpList = new ArrayList();
            String Query;
            try
            {
                connection.Open();
                //Pass the connection to a command object
                Query = "SELECT SpecialNeeds,WeeklyHrs FROM CLIENT WHERE ClientID=" + tmpClientIDParam + ";";
                SqlDataAdapter da = new SqlDataAdapter(Query, connection);

                DataSet ds = new DataSet();
                da.Fill(ds, "Client");

                //Saving the data from the fetched query in the ArrayList
                tmpList.Add(ds.Tables[0].Rows[0].ItemArray[0]);
                tmpList.Add(ds.Tables[0].Rows[0].ItemArray[1]);
            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::ReadClientNeedsAndWeekelyHours Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return tmpList;
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that Get all the certificates for a particular Homecare worker
        /// -------------------------------------------------------------------------------
        /// </summary>
        public ArrayList ReadCertsForId(long tmpHCWIDParam)
        {
            ArrayList ArrCertList = new ArrayList();
            String Query;
            try
            {
                connection.Open();
                //Pass the connection to a command object
                Query = "SELECT WorkerCertification.CertID, CertName, Description " +
                          " FROM WorkerCertification, Certification " +
                          " WHERE WorkerCertification.HCWID =  " + tmpHCWIDParam +
                          " AND WorkerCertification.CertID = Certification.CertID;";

                SqlDataAdapter da = new SqlDataAdapter(Query, connection);

                DataSet ds = new DataSet();
                da.Fill(ds, "WorkerCertification");
                //Feching the data returnedback from query and saving it in Certificate obj
                for (int iCntr = 0; iCntr < ds.Tables[0].Rows.Count; iCntr++)
                {
                    Certification CertObj = new Certification();
                    CertObj.CertID = int.Parse(ds.Tables[0].Rows[iCntr].ItemArray[0].ToString());
                    CertObj.CertName = ds.Tables[0].Rows[iCntr].ItemArray[1].ToString();
                    CertObj.CertDescription = ds.Tables[0].Rows[iCntr].ItemArray[2].ToString();
                    ArrCertList.Add(CertObj);
                }
            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::ReadCertsForId Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return ArrCertList;
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that Returns the Constraints and the weekely hours for Homecare Worker
        /// -------------------------------------------------------------------------------
        /// </summary>
        public HomeCareWorker ReadConstraintWeekHoursForHCW(long tmpHCWIDParam)
        {
            HomeCareWorker tmpHCWObj = new HomeCareWorker();
            String Query;
            try
            {
                connection.Open();

                //Pass the connection to a command object
                Query = "SELECT Constraints,WeeklyHrs FROM Worker" +
                          " WHERE Worker.HCWID =  " + tmpHCWIDParam;

                SqlDataAdapter da = new SqlDataAdapter(Query, connection);

                DataSet ds = new DataSet();
                da.Fill(ds, "Worker");

                //Saving the data got back from query in the object
                tmpHCWObj.SetContraints(ds.Tables[0].Rows[0].ItemArray[0].ToString());
                tmpHCWObj.SetWeeklyHours(int.Parse(ds.Tables[0].Rows[0].ItemArray[1].ToString()));

            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::ReadConstraintWeekHoursForHCW Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return tmpHCWObj;
        }


        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that Returns the Number of Remaining Hours for the client
        /// -------------------------------------------------------------------------------
        /// </summary>
        public int ReadClientRemainingHours(long tmpClientIDParam, String tmpDateParam, String tmpNextDateParam)
        {
            int RemHours = 0;
            String Query;

            try
            {
                connection.Open();
                //Pass the connection to a command object
                Query = "SELECT sum(EndHour-StartHour), Start from AvailabilityDetails, AvailabilityMaster" +
                          " WHERE AvailabilityMaster.UserId = " + tmpClientIDParam +
                          " AND AvailabilityDetails.AvlID = AvailabilityMaster.AvlId AND " +
                          " StartDate BETWEEN '" + tmpDateParam + "' AND '" + tmpNextDateParam +
                          " GROUP BY StartDate; ";

                SqlDataAdapter da = new SqlDataAdapter(Query, connection);

                DataSet ds = new DataSet();
                da.Fill(ds, "AvailabilityDetails");

                // Fetching the data returnedback from query and saving it integer variable to return 
                // value back to the calling function
                if (ds.Tables[0].Rows[0].IsNull(0) == false)
                {
                    RemHours = int.Parse(ds.Tables[0].Rows[0].ItemArray[0].ToString());
                }
            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::ReadClientRemainingHours Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return RemHours;
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that Read the HCW information depending on the firstname and/or the last name
        /// -------------------------------------------------------------------------------
        /// </summary>
        public ArrayList ReadHCWList(String tmpFirstNameParam, String tmpLastNameParam)
        {
            ArrayList HCWList = new ArrayList();
            String Query;
            try
            {
                connection.Open();
                //Pass the connection to a command object
                if (String.IsNullOrEmpty(tmpFirstNameParam))
                {
                    Query = "SELECT UserID, UserDetails.FirstName, UserDetails.LastName, UserDetails.City, " +
                              " UserDetails.State, UserDetails.DOB, UserDetails.PhoneNumber1 " +
                              " FROM UserDetails WHERE upper(UserDetails.LastName) = upper('" + tmpLastNameParam + "') " +
                              " AND UserType = 'H';";
                }
                else if (String.IsNullOrEmpty(tmpLastNameParam))
                {
                    Query = "SELECT UserID, UserDetails.FirstName, UserDetails.LastName, UserDetails.City, " +
                              " UserDetails.State, UserDetails.DOB, UserDetails.PhoneNumber1 " +
                              " FROM UserDetails WHERE upper(UserDetails.FirstName) = upper('" + tmpFirstNameParam + "') " +
                              " AND UserType = 'H';";
                }
                else
                {
                    Query = "Select UserID, UserDetails.FirstName, UserDetails.LastName, UserDetails.City, " +
                              " UserDetails.State, UserDetails.DOB, UserDetails.PhoneNumber1 " +
                              " FROM UserDetails WHERE upper(UserDetails.FirstName) = upper('" + tmpFirstNameParam + "') " + " AND upper(UserDetails.LastName)=upper('" + tmpLastNameParam + "') " +
                              " AND UserType = 'H';";
                }

                SqlDataAdapter da = new SqlDataAdapter(Query, connection);

                DataSet ds = new DataSet();
                da.Fill(ds, "User");

                //Saving the data recieved from the returned query into HCW object
                for (int iCntr = 0; iCntr < ds.Tables[0].Rows.Count; iCntr++)
                {
                    HomeCareWorker HCWObj = new HomeCareWorker();

                    long lngID = long.Parse(ds.Tables[0].Rows[iCntr].ItemArray[0].ToString());
                    HCWObj.SetUserID(lngID);

                    HCWObj.SetName(ds.Tables[0].Rows[iCntr].ItemArray[1].ToString(), ds.Tables[0].Rows[iCntr].ItemArray[2].ToString());
                    HCWObj.SetCity(ds.Tables[0].Rows[iCntr].ItemArray[3].ToString());
                    HCWObj.SetState(ds.Tables[0].Rows[iCntr].ItemArray[4].ToString());

                    DateTime tmpDateandTime = DateTime.Parse(ds.Tables[0].Rows[iCntr].ItemArray[5].ToString());
                    HCWObj.SetDateOfBirth(tmpDateandTime);

                    long PhoneNo = long.Parse(ds.Tables[0].Rows[iCntr].ItemArray[6].ToString());
                    HCWObj.SetPhoneNumber1(PhoneNo.ToString());
                    HCWList.Add(HCWObj);
                }
            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::ReadHCWList Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return HCWList;
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Function that finds homecare workers based on their availability and the
        /// tasks that they perform
        /// -------------------------------------------------------------------------------
        /// </summary>
        public ArrayList ReadMatchingAvailAndTasksWorker(AvailableTimeSlot preferredTime, ArrayList requestedTasks)
        {
            //Declaring a data reader
            SqlDataReader readHomecareWorkers = null;

            // Array that contains a list of macthing homecare worker ID's
            ArrayList matchingHomecareWorkerIDs = new ArrayList();

            try
            {

                //Obtaining the required parameters from the AvailableTimeslot object
                DayOfWeek preferredDay = preferredTime.GetDay();

                int startHour = preferredTime.GetStartHour();
                int startMinute = preferredTime.GetStartMinute();

                int endHour = preferredTime.GetEndHour();
                int endMinute = preferredTime.GetEndMinute();

                DateTime preferredDate = preferredTime.GetDate();
                DateTime preferredStartDate = preferredTime.GetBeginDate();
                DateTime preferredEndDate = preferredTime.GetEndDate();

                DateTime preferredStartDateTime = preferredTime.GetBeginDateTime();
                DateTime preferredEndDateTime = preferredTime.GetEndDateTime();

                //Obtaining the required parameters from the requested tasks object
                String taskList = "(";
                for (int tempIndex = 0; tempIndex < requestedTasks.Count; tempIndex++)
                {
                    if (tempIndex != 0)
                        taskList = taskList + ",";
                    Task newTask = requestedTasks[tempIndex] as Task;
                    taskList = taskList + newTask.GetTaskID();

                }
                taskList = taskList + ")";

                int numberOfTasks = requestedTasks.Count;

                // Open the connection
                connection.Open();

                // Initialising a string to the SLQ statement that finds all matching homecare workers
                String commandString = "SELECT DISTINCT UserID FROM UserDetails WHERE UserID IN " +
                    //Selecting homecare workers who are available on the given day at the given time
                                            "(SELECT W.HCWID FROM Worker AS W, AvailabilityMaster AS AM " +
                                            "WHERE W.HCWID = AM.UserID AND AM.AvlID IN " +
                                            "(SELECT AM.AvlID FROM AvailabilityMaster AS AM, AvailabilityDetails AS AD " +
                                            "WHERE AM.AvlID = AD.AvlID AND AM.StartDate <= '" + preferredStartDate + "' AND AM.EndDate >= '" + preferredEndDate +
                                            "' AND AD.Day = '" + preferredDay + "' AND ((AD.StartHour < " + startHour + ") OR (AD.StartHour = " + startHour + " AND AD.StartMinute <= " + startMinute +
                                            ")) AND ((AD.EndHour > " + endHour + ") OR (AD.EndHour = " + endHour + " AND AD.EndMinute >= " + endMinute + " )))" +
                    //Removing availabilities which have an entry in the avilability exceptions
                                            "AND AM.AvlID NOT IN" +
                                            "(SELECT AM.AvlID FROM AvailabilityMaster AS AM, AvailabilityExceptions AS AE " +
                                            "WHERE AM.AvlID = AE.AvlID AND AE.Date = '" + preferredDate + "') " +
                    //Removing availabilities which have an appointment scheduled for that day. 
                                            "AND AM.AvlID NOT IN " +
                                            "(SELECT AM.AvlID FROM AvailabilityMaster AS AM, Appointment AS A " +
                    // Consider a case where there is an appointment scheduled for that hoemcare worker from 9 to 10
                    //This catches those appointments when the preffered time is 9 to 10 or when the preferred time is 9:15 to 9:45
                                            "WHERE AM.UserID = A.HCWID AND ((A.StartTime <= '" + preferredStartDateTime + "' AND A.EndTime >= '" + preferredEndDateTime + "') OR " +
                    //This catches those cases when the preferred time is 8:30 to 9:30
                                            "(A.StartTime <= '" + preferredEndDateTime + "' AND A.EndTime >= '" + preferredEndDateTime + "') OR " +
                    //This catches those cases when the preferred time is 9:30 to 10:30
                                            "(A.StartTime <= '" + preferredStartDateTime + "' AND A.EndTime >= '" + preferredStartDateTime + "') OR " +
                    //This catches those cases when the preferred time is 8:30 to 10:30
                                            "(A.StartTime > '" + preferredStartDateTime + "' AND A.EndTime < '" + preferredEndDateTime + "')))) " +
                    //Select homecare workers with the required skills
                                            " AND UserID IN " +
                                            "(SELECT W.HCWID FROM Worker AS W, WorkerSkills as WS " +
                                            "WHERE W.HCWID = WS.HCWID AND WS.TASKID IN " + taskList +
                                            " GROUP BY W.HCWID HAVING COUNT(*) = " + numberOfTasks + " )";

                // Pass the connection to a command object
                SqlCommand command = new SqlCommand(commandString, connection);

                // Execute the SQL statement
                readHomecareWorkers = command.ExecuteReader();

                // For all matching homecare worker ID's found populate the Array of ID's that is to be sent back to the calling function
                while (readHomecareWorkers.Read())
                {
                    matchingHomecareWorkerIDs.Add(readHomecareWorkers[0]);
                }
            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("HomecareWorker::ReadHomecareWorkerByMatch Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the reader
                if (readHomecareWorkers != null)
                {
                    readHomecareWorkers.Close();
                }

                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return matchingHomecareWorkerIDs;
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Given a homecare worker's ID this function fetches all the details of the homecare worker
        /// from the database
        /// -------------------------------------------------------------------------------
        /// </summary>
        public HomeCareWorker ReadWorkerByID(long tmpHCWIDParam)
        {
            int homecareworkerID = (int)tmpHCWIDParam;
            HomeCareWorker homecareWorker = new HomeCareWorker();

            SqlDataReader readHomecareWorker = null;

            //Array containing a homecare workers properties that will be used by setWorkerDetails
            ArrayList workerProperties = new ArrayList();

            try
            {
                connection.Open();

                // Contruct the cammand string
                String commandString = "SELECT * FROM UserDetails WHERE UserID = " + tmpHCWIDParam;

                // Pass the connection to a command object
                SqlCommand command = new SqlCommand(commandString, connection);

                // Execute the SQL statement
                readHomecareWorker = command.ExecuteReader();

                if (readHomecareWorker.Read())
                {
                    homecareWorker.SetUserID((int)readHomecareWorker[0]);
                    homecareWorker.SetLastName(readHomecareWorker[1] as String);
                    homecareWorker.SetFirstName(readHomecareWorker[2] as String);
                    homecareWorker.SetMiddleName(readHomecareWorker[3] as String);
                    homecareWorker.SetDateOfBirth((DateTime)readHomecareWorker[4]);
                    homecareWorker.SetSexType(readHomecareWorker[5] as String);
                    homecareWorker.SetAddress1(readHomecareWorker[8] as String);
                    homecareWorker.SetAddress1(readHomecareWorker[9] as String);
                    homecareWorker.SetCity(readHomecareWorker[10] as String);
                    homecareWorker.SetState(readHomecareWorker[11] as String);
                    homecareWorker.SetZipCode(readHomecareWorker[12] as String);
                    homecareWorker.SetPhoneNumber1(readHomecareWorker[13] as String);
                    homecareWorker.SetPhoneNumber2(readHomecareWorker[14] as String);
                    homecareWorker.SetPhoneNumber3(readHomecareWorker[15] as String);
                    homecareWorker.SetEmail(readHomecareWorker[16] as String);
         
                    readHomecareWorker.Close();

                    // Contruct the cammand string
                    commandString = "SELECT * FROM Worker WHERE HCWID = " + homecareworkerID;


                    // Pass the connection to a command object
                    command = new SqlCommand(commandString, connection);

                    // Execute the SQL statement
                    readHomecareWorker = command.ExecuteReader();

                    if (readHomecareWorker.Read())
                    {
                        homecareWorker.SetContraints(readHomecareWorker[1] as String);
                        homecareWorker.SetWeeklyHours((int)readHomecareWorker[2]);
                    }
                }

            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("HomecareWorker::ReadWorkerByID Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the reader
                if (readHomecareWorker != null)
                {
                    readHomecareWorker.Close();
                }

                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }

            return homecareWorker;
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Given a Client's ID this function fetches all the name details of the Client
        /// from the database
        /// -------------------------------------------------------------------------------
        /// </summary>
        public Client ReadClientInfoByID(long tmpClientIDParam)
        {
            Client tmpClientObj = new Client(); ;
            String Query;
            Query = "Select * from UserDetails where UserID = " + tmpClientIDParam + ";";
            try
            {
                connection.Open();
                SqlDataAdapter da = new SqlDataAdapter(Query, connection);
                DataSet ds = new DataSet("UserDetails");
                da.Fill(ds, "UserDetails");

                tmpClientObj.SetFirstName(ds.Tables[0].Rows[0].ItemArray[2].ToString());
                tmpClientObj.SetMiddleName(ds.Tables[0].Rows[0].ItemArray[3].ToString());
                tmpClientObj.SetLastName(ds.Tables[0].Rows[0].ItemArray[1].ToString());
                //Added for more info to display Client Data
                tmpClientObj.SetDateOfBirth((DateTime)ds.Tables[0].Rows[0].ItemArray[4]);
                tmpClientObj.SetSexType(ds.Tables[0].Rows[0].ItemArray[5].ToString());
                tmpClientObj.SetAddress1(ds.Tables[0].Rows[0].ItemArray[8].ToString());
                tmpClientObj.SetCity(ds.Tables[0].Rows[0].ItemArray[10].ToString());
                tmpClientObj.SetState(ds.Tables[0].Rows[0].ItemArray[11].ToString());
                tmpClientObj.SetZipCode(ds.Tables[0].Rows[0].ItemArray[12].ToString());
                tmpClientObj.SetPhoneNumber1(ds.Tables[0].Rows[0].ItemArray[13].ToString());
                tmpClientObj.SetEmail(ds.Tables[0].Rows[0].ItemArray[16].ToString());
            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::ReadClientInfoByID Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return tmpClientObj;
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Get all the tasks for particular Homecare Worker
        /// -------------------------------------------------------------------------------
        /// </summary>
        public ArrayList ReadAllTasksForHCW(long tmpHCWIDParam)
        {
            ArrayList ArrTaskList = new ArrayList();
            String Query;
            try
            {
                connection.Open();
                //Pass the connection to a command object
                Query = "SELECT WorkerSkills.TaskID, TaskName FROM Task, WorkerSkills " +
                          " WHERE WorkerSkills.HCWID =  " + tmpHCWIDParam +
                          " AND WorkerSkills.TaskID = Task.TaskID;";

                SqlDataAdapter da = new SqlDataAdapter(Query, connection);

                DataSet ds = new DataSet();
                da.Fill(ds, "Task");
                for (int iCntr = 0; iCntr < ds.Tables[0].Rows.Count; iCntr++)
                {
                    Task TaskObj = new Task();

                    TaskObj.SetTaskID(int.Parse(ds.Tables[0].Rows[iCntr].ItemArray[0].ToString()));
                    TaskObj.SetTaskName(ds.Tables[0].Rows[iCntr].ItemArray[1].ToString());
                    ArrTaskList.Add(TaskObj);
                }
            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::ReadAllTasksForHCW Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return ArrTaskList;
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Returns the appointments' information on a View Date
        /// -------------------------------------------------------------------------------
        /// </summary>
        public ArrayList ReadApptList(DateTime tmpstartViewdate)
        {
            //store the start & end view date in strings as "MM?DD?YYYY" format
            string dateToViewAppt = tmpstartViewdate.ToString("d");
            string dateToEndViewAppt = tmpstartViewdate.AddDays(1).ToString("d");
            String Query;

            //Array containing Appointments
            ArrayList apptList = new ArrayList();

            try
            {
                connection.Open();
                //Querying only appointments in a particular date
                Query = "SELECT * FROM Appointment WHERE StartTime >= '" + dateToViewAppt +
                    "' AND StartTime < '" + dateToEndViewAppt + "'";

                SqlDataAdapter da = new SqlDataAdapter(Query, connection);
                DataSet ds = new DataSet("AppointmentDetails");
                da.Fill(ds, "AppointmentDetails");

                //Fill in the data
                for (int iCntr = 0; iCntr < ds.Tables[0].Rows.Count; iCntr++)
                {
                    //Create Appointment object to save all the appointment info
                    Appointment apptObj = new Appointment();

                    long lngID = long.Parse(ds.Tables[0].Rows[iCntr].ItemArray[1].ToString());
                    apptObj.SetClient(Library.Client.GetClientByID(lngID));
                    lngID = long.Parse(ds.Tables[0].Rows[iCntr].ItemArray[2].ToString());

                    apptObj.SetHomeCareWorker(Library.HomeCareWorker.GetWorkerByID(lngID));

                    apptObj.SetBeginDateTime(DateTime.Parse(ds.Tables[0].Rows[iCntr].ItemArray[3].ToString()));
                    apptObj.SetEndDateTime(DateTime.Parse(ds.Tables[0].Rows[iCntr].ItemArray[4].ToString()));
                    apptList.Add(apptObj);
                }
            }

            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::ReadApptList Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return apptList;
        }

        /// <summary>
        /// -------------------------------------------------------------------------------
        /// This function fetches a task from the database whose name matches that present in the database
        /// -------------------------------------------------------------------------------
        /// </summary>
        public Task ReadTaskByName(String tmptaskNameParam)
        {
            // The Task object that will be returned to the calling function
            Task newTask = new Task();

            // The SQL reader
            SqlDataReader readTask = null;

            try
            {
                connection.Open();

                // Contruct the cammand string
                String commandString = "SELECT * FROM Task WHERE TaskName LIKE '" + tmptaskNameParam + "'";

                // Pass the connection to a command object
                SqlCommand command = new SqlCommand(commandString, connection);

                // Execute the SQL statement
                readTask = command.ExecuteReader();

                if (readTask.Read())
                {
                    newTask.SetTaskID((int)readTask[0]);
                    newTask.SetTaskName(readTask[1] as String);
                    newTask.SetTaskDescription(readTask[2] as String);
                }
            }
            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::ReadTaskByName Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the reader
                if (readTask != null)
                {
                    readTask.Close();
                }

                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return newTask;
        }


        /// <summary>
        /// -------------------------------------------------------------------------------
        /// Returns the client's appointments information on a View Date
        /// -------------------------------------------------------------------------------
        /// </summary>
        public ArrayList ReadClientApptList(Client client, DateTime startViewdate, DateTime endViewDate)
        {
            //Getting the start & end to View appt in a string in "MM/DD/YYYY" format
            string dateToViewAppt = startViewdate.ToString("d");
            string dateToEndViewAppt = endViewDate.ToString("d");
            String Query;
            long clientID = client.GetClientId();   //Get the client ID

            //Array containing Appointments
            ArrayList apptList = new ArrayList();

            try
            {
                connection.Open();
                //Querying only the appointment with matching clientID and anything between the start & end date
                Query = "SELECT * FROM Appointment WHERE ClientID = '" + clientID + "' and StartTime >= '" + dateToViewAppt +
                    "' AND StartTime < '" + dateToEndViewAppt + "'";

                SqlDataAdapter da = new SqlDataAdapter(Query, connection);
                DataSet ds = new DataSet("AppointmentDetails");
                da.Fill(ds, "AppointmentDetails");

                for (int iCntr = 0; iCntr < ds.Tables[0].Rows.Count; iCntr++)
                {
                    //Creating Appointment object to save all the information
                    Appointment apptObj = new Appointment();

                    long lngID = long.Parse(ds.Tables[0].Rows[iCntr].ItemArray[2].ToString());
                    apptObj.SetHomeCareWorker(Library.HomeCareWorker.GetWorkerByID(lngID));
                    apptObj.SetBeginDateTime(DateTime.Parse(ds.Tables[0].Rows[iCntr].ItemArray[3].ToString()));
                    apptObj.SetEndDateTime(DateTime.Parse(ds.Tables[0].Rows[iCntr].ItemArray[4].ToString()));
                    apptList.Add(apptObj);
                }
            }

            catch (Exception ExceptionMsg)
            {
                LOG.error("Utility::ReadClientApptList Exception handled: " + ExceptionMsg.ToString());
            }
            finally
            {
                // Close the connection
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return apptList;
        }
        
        // -------------------------------------------------------------------------------
        // Private Data Members
        // -------------------------------------------------------------------------------
        SqlConnection connection = new SqlConnection();
    }
}